﻿using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using SLS.DataBase;
using SLS.ExClassLib.Events;
using LanSunStudio.ExClassLib;
using SLS.ExClassLib.Message;
using SLS.ExClassLib.WCFClient;
namespace SLS.ExClassLib.Database
{
    /// <summary>
    /// Class that holds everything that need to be binded to the main window
    /// </summary>
    public partial class DataAccessService
    {
        public DataSet QueryData(MessageEx msg, bool ModelReturn = false, bool blSave = true)
        {
            DataSet setReturn = new DataSet();            
            DataTable tableQueryCond = null;
            string JobID = "";
            string TBName = "";
            string CONDTYPE;
            DataRow rowQueryCond = null;
            try
            {
                JobID = msg.JobID;
                TBName = JobID.Replace("OnQuery", "");
                TBName = TBName.Replace("OnFileQuery","");
                if (TBName =="")
                {
                    TBName = msg.Tag as string;

                }
                if (JobID != "OnSaveRecord")
                {
                    if (setGlobal.Tables.Contains(TBName))
                    {
                        setReturn.Merge(setGlobal.Tables[TBName].Clone());
                    }
                    else
                    {
                        //Tracer.
                    }
                }
                //Tracer.Verbose(logRoute + ".QueryData :", msgRequest.ToString());
                DataSet set2Query = msg.setEvent;
                tableQueryCond = set2Query.Tables[TBName + "QueryCond"];
                if (tableQueryCond != null)
                {
                    rowQueryCond = tableQueryCond.Rows[0];
                   CONDTYPE=rowQueryCond["CONDTYPE"].ToString();
                   if (localTableList.Contains(TBName + ";")
                        && !CONDTYPE.Contains("QueryCond")
                        )
                    {
                        setReturn = QueryLocalDataByLINQ(rowQueryCond);
                        return setReturn;
                    }
                }
                MessageEx  msgr= ((IERPBLayerService)BLClient).AskService(msg);
                setReturn.Merge(msgr.setEvent); 
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet QueryLocalDataByLINQ(DataRow rowQueryCond, bool ModelReturn = false, bool blSave = true)
        {
            DataSet setReturn = new DataSet();
            DataRow rowForeignKey;
            string QueryType, CONDTYPE;
            string tableTarget = "";
            try
            {
                QueryType = rowQueryCond["QUERYTYPE"].ToString().Trim();
                CONDTYPE = rowQueryCond["CONDTYPE"].ToString().Trim();
                tableTarget = rowQueryCond.Table.TableName;
                tableTarget = tableTarget.Replace("QueryCond", "");
                DataRow rowData = MapDataRow("", "", tableTarget, rowQueryCond, null, null, true);
                rowData["SM"] = rowQueryCond["SM"];
                dwForeignKeyLists = TBForeignKeyList.Select("TABLENAME='" + tableTarget + "' and FOREIGNKEY='" + CONDTYPE + "'");
                if (dwForeignKeyLists.Count() > 0)
                {
                    rowForeignKey = dwForeignKeyLists[0];
                }
                else
                {
                    return setReturn;
                }
                setReturn = BuildAndRunCmdLINQ(rowData, rowForeignKey);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet BuildAndRunCmdLINQ(DataRow rowRequest, DataRow rowForeignKey)
        {
            string expression="";
            string tableRequest=rowRequest.Table.TableName; 
            DataSet dsResult = null;
 
            try
            {
                expression = GetConditions(rowRequest,rowForeignKey);
                DataRow[] rowsLinq = setGlobal.Tables[tableRequest].Select(expression); 
                dsResult = new DataSet();
                dsResult.Merge(rowsLinq);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsResult;
        }
        private string GetConditions(DataRow rowData,DataRow rowForeignKey)
        {
            string expression = "", TBKey;
 
            try
            {
                for (int KeyI = 0; KeyI < 10; KeyI++)
                {
                    string KeyName = "KEY" + KeyI.ToString();
                    TBKey = rowForeignKey[KeyName].ToString().Trim();
                    if (TBKey != "")
                    {
                        expression += TBKey + "='" + rowData[TBKey].ToString() + "' and ";
                    }
                }
                expression += "***";
                expression=expression.Replace(" and ***", "");
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return expression;
        }
        private SqlCommand BuildSQLParameters(SqlCommand cmdSQL, string strServiceType, DataRow dwForeignKeyList, DataRow dwRequest)
        {
            if (dwRequest == null)
                return cmdSQL;
            string TBKey;
            string ForeignKey = dwForeignKeyList["FOREIGNKEY"].ToString().Trim();
            string TBName = dwRequest.Table.TableName;
            System.DateTime TimeNow = System.DateTime.Now;
            string sValue = "";
            try
            {
                #region CaseWise GDKEYID/TBNameKey

                if (strServiceType.Contains("UPDATE")
                || strServiceType.Contains("INSERT"))
                {
                    if (dwRequest.Table.Columns.Contains("GDKEYID"))
                    {
                        sValue = dwRequest["GDKEYID"].ToString().Trim();
                        if (sValue == "")
                            dwRequest["GDKEYID"] = dwRequest["POS"];
                    }
                    if (dwRequest.Table.Columns.Contains(TBName + "ID"))
                    {
                        sValue = dwRequest[TBName + "ID"].ToString().Trim();
                        if (sValue == "")
                            dwRequest[TBName + "ID"] = dwRequest["POS"];
                    }
                    if (dwRequest.Table.Columns.Contains(TBName + "KEY")
                        && TBName != "GTDEF")
                    {
                        sValue = dwRequest[TBName + "KEY"].ToString().Trim();
                        if (sValue == "")
                            dwRequest[TBName + "KEY"] = dwRequest["GDKEY"];
                    }
                }
                #endregion

                if (strServiceType.Contains("UPDATE")
                    || strServiceType.Contains("INSERT")
                    || strServiceType.Contains("SELECT_LIKE")
                    || ForeignKey.Contains("_LikeAny"))
                {
                    foreach (DataColumn dcTemp in dwRequest.Table.Columns)
                    {
                        TBKey = dcTemp.ColumnName;
                        cmdSQL.Parameters.Add(new SqlParameter("@" + TBKey, dwRequest[TBKey].ToString()));
                    }
                    TimeNow = System.DateTime.Now;
                    if (strServiceType.Contains("INSERT"))
                    {
                        cmdSQL.Parameters["@FRSTDATE"].Value = TimeNow;
                    }
                    cmdSQL.Parameters["@LASTDATE"].Value = TimeNow;
                }
                else
                {
                    for (int KeyI = 0; KeyI < 10; KeyI++)
                    {
                        string KeyName = "KEY" + KeyI.ToString();
                        TBKey = dwForeignKeyList[KeyName].ToString().Trim();
                        if (TBKey != "")
                        {
                            cmdSQL.Parameters.Add(new SqlParameter("@" + TBKey, dwRequest[TBKey].ToString()));
                        }
                    }
                    TimeNow = System.DateTime.Now;
                    if (!cmdSQL.Parameters.Contains("@FRSTUSER"))
                    {
                        cmdSQL.Parameters.Add(new SqlParameter("@FRSTUSER", "LANSUN"));
                        cmdSQL.Parameters.Add(new SqlParameter("@FRSTDATE", TimeNow));
                        cmdSQL.Parameters.Add(new SqlParameter("@LASTUSER", "LANSUN"));
                        cmdSQL.Parameters.Add(new SqlParameter("@LASTDATE", TimeNow));
                    }
                    else
                    {
                        cmdSQL.Parameters["@FRSTDATE"].Value = TimeNow;
                        cmdSQL.Parameters["@LASTDATE"].Value = TimeNow;
                    }
                }

                if (!strServiceType.Contains("SELECT") && !strServiceType.Contains("STRUCTURE"))
                {
                    cmdSQL = Build(cmdSQL, dwRequest);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return cmdSQL;
        }
        private DataSet NameTable(string strServiceTargetTableID, string strServiceConditions, DataSet dsSource, string strtable_name_list)
        {
            string strServiceTargetTable;

            try
            {

                int strPosX = 0;
                int strPosY = 0;
                int i = 0;
                int iTmp = 0;

                if (dsSource.Tables.Count > 0)
                {
                    while (strtable_name_list != "")
                    {
                        iTmp = strtable_name_list.Length;
                        strPosX = strtable_name_list.IndexOf(";");
                        strServiceTargetTable = strtable_name_list.Substring(0, strPosX + 1);
                        strtable_name_list = strtable_name_list.Substring(strPosX + 1, iTmp - (strPosX + 1));
                        //strtable_name_list = strtable_name_list.Replace(strServiceTargetTable, "");
                        strServiceTargetTable = strServiceTargetTable.Replace(";", "");

                        strPosY = strServiceConditions.IndexOf(";");
                        if (strPosY == -1)
                        {
                            strServiceConditions = strServiceTargetTableID + ";";
                            strPosY = strServiceConditions.IndexOf(";");
                        }
                        strServiceTargetTableID = strServiceConditions.Substring(0, strPosY + 1);
                        strServiceConditions = strServiceConditions.Replace(strServiceTargetTableID, "");
                        strServiceTargetTableID = strServiceTargetTableID.Replace(";", "");

                        if (dsSource.Tables[i] != null)
                        {
                            dsSource.Tables[i].TableName = strServiceTargetTable;
                            dsSource.Tables[i].DisplayExpression = strServiceTargetTableID;
                            dsSource.Tables[i].ExtendedProperties.Add("MakeUp", "NO");
                        }
                        i++;
                    }
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return dsSource;
        }
        public SqlCommand Build(SqlCommand cmdSQL, DataRow dwRequest)
        {
            string TBName = dwRequest.Table.TableName;
            Queue PKQueue = GetPrimaryKey(TBName);
            string PKey = "";
            while (PKQueue.Count > 0)
            {
                PKey = (string)PKQueue.Dequeue();
            }
            return cmdSQL;

        }
        private Queue GetPrimaryKey(string TBName)
        {
            Queue cmbPath = new Queue();
            try
            {
                DataTable ForeignKeyList = this.setGlobal.FOREIGNKEYLIST;

                DataRow[] dwPK = ForeignKeyList.Select("TYPE='PrimaryKey' and TABLENAME='" + TBName + "' and FOREIGNKEY='PK_" + TBName + "'");

                for (int i = 0; i < 10; i++)
                {
                    string value = dwPK[0]["KEY" + i.ToString()].ToString().Trim();
                    if (value != "")
                    {
                        cmbPath.Enqueue(value);
                    }
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }

            return cmbPath;

        }
        public string GetPrimaryKeyValue(DataRow dwModel)
        {
            string TBName = dwModel.Table.TableName;
            string KeyValueString = TBName+".";
            try
            {

                DataRow[] dwPK = TBForeignKeyList.Select("TYPE='PrimaryKey' and TABLENAME='" + TBName + "' and FOREIGNKEY='PK_" + TBName + "'");

                for (int i = 0; i < 10; i++)
                {
                    string columnkey = dwPK[0]["KEY" + i.ToString()].ToString().Trim();
                    if (columnkey != "")
                    {
                        KeyValueString += dwModel[columnkey].ToString().Trim()+".";
                    }
                }
                KeyValueString += "***";
                KeyValueString = KeyValueString.Replace(".***", "");
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }

            return KeyValueString;

        }

        public object LoadForeignKeyList(string TBName, DataSet setSource)
        {
            try
            {
                var query = from dwForeignKeyList in DataAccessService.Instance.GlobalDataSet.Tables["ForeignKeyList"].AsEnumerable()
                            where dwForeignKeyList.Field<string>("TBNAME") == TBName
                            orderby "TBNAME"
                            select dwForeignKeyList
                       ;
                if (query.Count() > 0)
                {
                    foreach (DataRow dwForeignKeyList in query)
                    {
                        setSource = (SLDBSET)DataAccessService.Instance.ImportDataTable(setSource, dwForeignKeyList);
                    }
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setSource;
        }
        /// <summary>
        /// Add a log to the log stack, and manage the max value
        /// </summary>
        /// <param name="log"></param>
        public void AddLogEvent(MessageInfo log)
        {
            _LogEvents.Add(log);

            if (_LogEvents.Count > 180)
                _LogEvents.RemoveRange(0, 50);
        }
    }
}
